package cn.iune.erp.sale;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Row;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;



@Repository
public class SaleOrderDaoJPAImpl extends BaseJPAImpl<SaleOrder> implements ISaleOrderDao {

//	private final Logger log = LoggerFactory.getLogger(SaleOrderDaoJPAImpl.class);
	
//	private String kSql;
//	@PersistenceContext 
//	protected EntityManager em ;//


	@Override
	public void insert(SaleOrder obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(SaleOrder.class, fID));
	}
	
	@Override
	public SaleOrder update(SaleOrder obj){
		SaleOrder objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public SaleOrder getByID(String fID) {
		SaleOrder singleResult = em.find(SaleOrder.class, fID);
		return singleResult;
	}

	@Override
	public List<SaleOrder> getAll() {
		Query query = em.createNamedQuery("SaleOrder.findAll");
		@SuppressWarnings("unchecked")
		List<SaleOrder> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public SaleOrder getByNo(String orderNo, String enterpriseNo) {
		//1、定义sql语句
		String kSql="SELECT SaleOrder FROM SaleOrder SaleOrder "
				+ " WHERE ( SaleOrder.orderNo = :orderNo ) "
				+ " AND ( SaleOrder.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("orderNo", orderNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		SaleOrder singleResult = null;
		try{
			singleResult = (SaleOrder) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
		}
		return singleResult;
	}

	//根据门店流水号获取客户订单
	//这个不能使用，因为可能存在多个订单的情况
	/*
	@Override
	public SaleOrder getByGuestNumber(String guestNumber, String enterpriseNo) {
		//1、定义sql语句
		String kSql="SELECT SaleOrder FROM SaleOrder SaleOrder "
				+ " WHERE ( SaleOrder.guestNumber = :guestNumber ) "
//				+ " AND ( SaleOrder.factoryID = :factoryID ) "
				+ " AND ( SaleOrder.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("guestNumber", guestNumber);
//		query.setParameter("factoryID", factoryID);
		query.setParameter("enterpriseNo", enterpriseNo);
		SaleOrder singleResult = null;
		try{
			singleResult = (SaleOrder) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
		}
		return singleResult;
	}
	*/
	

	//统计订单数量
	@Override
	public long getCount(String sLoginName, String handling) {
		
		//1、定义sql语句
		String kSql="SELECT COUNT(*) FROM SaleOrder SaleOrder "
				+ " WHERE ( SaleOrder.sLoginName = :sLoginName ) "
				+ " AND SaleOrder.handling=:handling "
				;

		Map<String,Object> params = new HashMap<String,Object>();
		params.put("sLoginName", sLoginName);
		params.put("handling", handling);
		
		long count = super.getCount(kSql, params);
		
		return count;
	}

	@Override
	public List<SaleOrder> getByLoginName(String sLoginName) {
		//1、定义sql语句
		String kSql="SELECT SaleOrder FROM SaleOrder SaleOrder "
				+ " WHERE ( SaleOrder.sLoginName = :sLoginName ) "
				+ " ORDER BY SaleOrder.cDatetime DESC "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("sLoginName", sLoginName);
		@SuppressWarnings("unchecked")
		List<SaleOrder> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<SaleOrder> getBySsID(String ssID) {
		//1、定义sql语句
		String kSql="SELECT SaleOrder FROM SaleOrder SaleOrder "
				+ " WHERE ( SaleOrder.ssID = :ssID ) "
				+ " ORDER BY SaleOrder.cDatetime DESC "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("ssID", ssID);
		@SuppressWarnings("unchecked")
		List<SaleOrder> resultList = query.getResultList();
		return resultList;
	}


	//月销售额
	@Override
	public ArrayList<BigDecimal> getAmountMonth(String year,ArrayList<String> xAxisData, String enterpriseNo) {
		// TODO Auto-generated method stub

		String format = "SELECT %s FROM %s %s %s ";
		String select = "SUM(SaleOrder.orderAmountTotal) ";
		String from = " SaleOrder SaleOrder ";
		String where = " WHERE SaleOrder.enterpriseNo='" + enterpriseNo+  "'"
				+ " AND SaleOrder.audit='confirm' " 
				//JPA不支持直接用查询日期字段
				//DATE_FORMAT这个函数是日期转字符串的，但是转完了格式为2016-3而不是2016-03，调整格式参数也没有用
				+ " AND DATE_FORMAT(SaleOrder.confirmDate,'%Y-%c') = ? " 
				;
		
		String kSql = String.format(format, select, from, where, "");
//		log.info("kSql-->"+kSql);
		ArrayList<BigDecimal> seriesData = new ArrayList<BigDecimal>();
		for(String month:xAxisData){
			month = year + "-" + month.substring(0, month.length()-1);
//			month = "%" + month + "%";
			Query query = em.createQuery(kSql);
			query.setParameter(1, month);
//			query.setParameter("month", month);
			BigDecimal singleResult = (BigDecimal) query.getSingleResult();
			if(singleResult==null){
				singleResult = BigDecimal.ZERO;
			}
			seriesData.add(singleResult);
		}
		
		return seriesData;
	}

	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(SaleOrder.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);

	}

	@Override
	public JSONObject query(JSONObject data) throws SQLException {
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String sLoginName = data.getString("sLoginName");
		
//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SaleOrder.*"
//				+ ",FileShipper.shipperNo"
//				+ ",FileAddress.beingUsed"
				//截取第2个‘/’之前的所有字符，然后再截取第1个‘/’之后的所有字符（从右向左数）
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',2),'/',-1) as fRegion"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',3),'/',-1) as fProvince"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',4),'/',-1) as fCity"
//				+ ",substring_index(SaleOrder.fAddress,'/',-1) as fZone"
				
				//预收款待收金额
//				+ ",CONVERT((SaleOrder.orderAmountTotal - SaleOrder.discountAmount - SaleOrder.predictAmount),decimal(38,2)) as pendingAmount "
				;
		String from = "SaleOrder SaleOrder";
//		String from = "SaleOrder SaleOrder LEFT JOIN FileAddress FileAddress ON (FileAddress.fAddressName = SaleOrder.fAddressName AND FileAddress.factoryID = SaleOrder.factoryID)"
//				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("SaleOrder.orderNo LIKE ? OR SaleOrder.guestNumber LIKE ? OR SaleOrder.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("SaleOrder.fID='"+id+"'");
		}
		//处理sLoginName
		if (Utils.isNotEmptyString(sLoginName)) {
			filters.add("SaleOrder.sLoginName='"+sLoginName+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters");
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
//		System.out.println("filters-->"+filters);
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		
//		System.out.println("where-->"+where);

		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("SaleOrder");
		
		JSONObject json = Transform.tableToJson(table);
		//////////////////////////////////自定义聚合数据开始/////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////
		String aggregateSelect = "";
		
		JSONObject aggregateColumns = data.getJSONObject("aggregateColumns"); // 聚合列定义
//		System.out.println("aggregateColumns-->"+aggregateColumns);
		if(aggregateColumns!=null){
			
			Set<String> keys = aggregateColumns.keySet();
			int index = 0;
			for(String key:keys){
				if(index==0){
					aggregateSelect = aggregateSelect + "SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}else{
					aggregateSelect = aggregateSelect + ",SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}
				
				index++;
			}
			
//			aggregateSelect = "SUM(StorageItem.quantityL) as sumQuantityL,SUM(CONVERT(FileItem.costingAmountPer*StorageItem.quantityL,decimal(38,2))) as sumCostingAmount,SUM(StorageItem.pieceQuantityL) as sumPieceQuantityL";
			
		}
		
//		String aggregate = data.getString("aggregate");
//		if(aggregate!=null){
//			if(aggregateSelect.equals("")){
//				aggregateSelect = aggregate;
//			}else{
//				aggregateSelect = aggregateSelect + "," + aggregate;
//			}
//		}
		
//		System.out.println("aggregateSelect-->"+aggregateSelect);
		
		if(!aggregateSelect.equals("")){
			//如果select不为空
			
			String aggregateSql = String.format(format, aggregateSelect, from, where, "");
//			System.out.println("aggregateSql-->"+aggregateSql);
			Table aggregateTable = DataUtils.queryData(conn, aggregateSql, sqlParams, aggregateColumns, 0, table.getTotal());
//			Row row = aggregateTable.getRows().get(0);
//			JSONObject aggregateValues = Transform.rowToJson(aggregateTable, row);
			JSONObject jo = Transform.tableToJson(aggregateTable);
			JSONArray jarray = jo.getJSONArray("rows");
			JSONObject aggregateValues = new JSONObject();
			if(jarray.size()>0){
				aggregateValues = jarray.getJSONObject(0);
			}
//			System.out.println("aggregateValues-->"+aggregateValues);
			//jb-->{"userdata":{"recordState":"none"},"costingAmount":{"value":"599517613.43"},"costingAmountPer":{"value":"59957.893727"}}
			json.put("aggregateValues", aggregateValues);
		}
		//////////////////////////////////////////////////////////////////////////////////
		////////////////////////////自定义聚合数据结束////////////////////////////////////
		//关闭连接
		conn.close();
		
		return json;
	}

	@Override
	public JSONObject joint(JSONObject data) throws SQLException {
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String sLoginName = data.getString("sLoginName");
		
//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SaleOrder.*"
//				+ ",FileShipper.shipperNo"
//				+ ",FileAddress.beingUsed"
				//截取第2个‘/’之前的所有字符，然后再截取第1个‘/’之后的所有字符（从右向左数）
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',2),'/',-1) as fRegion"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',3),'/',-1) as fProvince"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',4),'/',-1) as fCity"
//				+ ",substring_index(SaleOrder.fAddress,'/',-1) as fZone"
				
				//预收款待收金额
//				+ ",CONVERT((SaleOrder.orderAmountTotal - SaleOrder.discountAmount - SaleOrder.predictAmount),decimal(38,2)) as pendingAmount "
				;
//		String from = "SaleOrder SaleOrder";
		String from = "SaleOrder SaleOrder"
				+ " LEFT JOIN ServiceSession ServiceSession ON ServiceSession.fID = SaleOrder.ssID "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("SaleOrder.orderNo LIKE ? OR SaleOrder.guestNumber LIKE ? OR SaleOrder.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("SaleOrder.fID='"+id+"'");
		}
		//处理sLoginName
		if (Utils.isNotEmptyString(sLoginName)) {
			filters.add("SaleOrder.sLoginName='"+sLoginName+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters");
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
//		System.out.println("filters-->"+filters);
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		
//		System.out.println("where-->"+where);

		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("SaleOrder");
		
		JSONObject json = Transform.tableToJson(table);
		//////////////////////////////////自定义聚合数据开始/////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////
		String aggregateSelect = "";
		
		JSONObject aggregateColumns = data.getJSONObject("aggregateColumns"); // 聚合列定义
//		System.out.println("aggregateColumns-->"+aggregateColumns);
		if(aggregateColumns!=null){
			
			Set<String> keys = aggregateColumns.keySet();
			int index = 0;
			for(String key:keys){
				if(index==0){
					aggregateSelect = aggregateSelect + "SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}else{
					aggregateSelect = aggregateSelect + ",SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}
				
				index++;
			}
			
//			aggregateSelect = "SUM(StorageItem.quantityL) as sumQuantityL,SUM(CONVERT(FileItem.costingAmountPer*StorageItem.quantityL,decimal(38,2))) as sumCostingAmount,SUM(StorageItem.pieceQuantityL) as sumPieceQuantityL";
			
		}
		
//		String aggregate = data.getString("aggregate");
//		if(aggregate!=null){
//			if(aggregateSelect.equals("")){
//				aggregateSelect = aggregate;
//			}else{
//				aggregateSelect = aggregateSelect + "," + aggregate;
//			}
//		}
		
//		System.out.println("aggregateSelect-->"+aggregateSelect);
		
		if(!aggregateSelect.equals("")){
			//如果select不为空
			
			String aggregateSql = String.format(format, aggregateSelect, from, where, "");
//			System.out.println("aggregateSql-->"+aggregateSql);
			Table aggregateTable = DataUtils.queryData(conn, aggregateSql, sqlParams, aggregateColumns, 0, table.getTotal());
//			Row row = aggregateTable.getRows().get(0);
//			JSONObject aggregateValues = Transform.rowToJson(aggregateTable, row);
			JSONObject jo = Transform.tableToJson(aggregateTable);
			JSONArray jarray = jo.getJSONArray("rows");
			JSONObject aggregateValues = new JSONObject();
			if(jarray.size()>0){
				aggregateValues = jarray.getJSONObject(0);
			}
//			System.out.println("aggregateValues-->"+aggregateValues);
			//jb-->{"userdata":{"recordState":"none"},"costingAmount":{"value":"599517613.43"},"costingAmountPer":{"value":"59957.893727"}}
			json.put("aggregateValues", aggregateValues);
		}
		//////////////////////////////////////////////////////////////////////////////////
		////////////////////////////自定义聚合数据结束////////////////////////////////////
		//关闭连接
		conn.close();
		
		return json;
	}

	@Override
	public JSONObject querySimple(JSONObject data) throws SQLException {
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String sLoginName = data.getString("sLoginName");
		
//		log.info("limit-->"+limit);
//		log.info("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SaleOrder.*"
//				+ ",FileShipper.shipperNo"
//				+ ",FileAddress.beingUsed"
				//截取第2个‘/’之前的所有字符，然后再截取第1个‘/’之后的所有字符（从右向左数）
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',2),'/',-1) as fRegion"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',3),'/',-1) as fProvince"
//				+ ",substring_index(substring_index(SaleOrder.fAddress,'/',4),'/',-1) as fCity"
//				+ ",substring_index(SaleOrder.fAddress,'/',-1) as fZone"
				
				;
//		String from = "SaleOrder SaleOrder"
		String from = "SaleOrder SaleOrder "
//				+ " LEFT JOIN GuestOffline GuestOffline ON (GuestOffline.guestNumber = SaleOrder.guestNumber AND GuestOffline.enterpriseNo = SaleOrder.enterpriseNo)"
//				+ " LEFT JOIN ServiceSession ServiceSession ON (GuestOffline.fMasterID = ServiceSession.fID )"
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("SaleOrder.orderNo LIKE ? OR SaleOrder.comments LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 2; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("SaleOrder.fID='"+id+"'");
		}
		//处理sLoginName
		if (Utils.isNotEmptyString(sLoginName)) {
			filters.add("SaleOrder.sLoginName='"+sLoginName+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters");
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
//		System.out.println("filters-->"+filters);
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		
//		System.out.println("where-->"+where);

		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		table.setOffset(offset);
		table.setTableName("SaleOrder");
		
		//JSONObject json = Transform.tableToJson(table);
		
		JSONArray arrayItem = new JSONArray();

		List<Row> list = table.getRows();
		for(Row row : list){
			JSONObject javaObject= Transform.rowToJavaObject(table, row);
			SaleOrder obj = JSON.toJavaObject(javaObject, SaleOrder.class);
			
			arrayItem.add(obj);
		}
		
		JSONObject json = new JSONObject();
		
		json.put("data", arrayItem);
		json.put("total", Integer.parseInt(total.toString()));
		json.put("code", 0);
		json.put("msg", "success");
		
		return json;
	}


	//待审核单据数量
	@Override
	public long getUnAgreeCount(String enterpriseNo) {
		// TODO Auto-generated method stub
		String sql = "SELECT COUNT(*) FROM SaleOrder SaleOrder "
				+ " WHERE SaleOrder.state='confirm' "
				+ " AND SaleOrder.audit='active' "
				+ " AND SaleOrder.status='open' "
				+ " AND SaleOrder.enterpriseNo = :enterpriseNo "
				;
		Query query = em.createQuery(sql);
		query.setParameter("enterpriseNo", enterpriseNo);
		long singleResult =  (long) query.getSingleResult();
		return singleResult;
	}


	//待支付的单据总金额
	@Override
	public BigDecimal getUnpaidXSDDAmount(String customerID, String enterpriseNo) {
		// TODO Auto-generated method stub
		String sql = "SELECT SUM(SaleOrder.orderAmountTotal + SaleOrder.freightAmount - SaleOrder.discountCash) FROM SaleOrder SaleOrder "
				+ " WHERE  SaleOrder.audit='confirm' "
				+ " AND SaleOrder.depositRate>0 "//预付款订单
				+ " AND SaleOrder.paymentStatus='unpaid' "
				+ " AND SaleOrder.customerID = :customerID "
				+ " AND SaleOrder.enterpriseNo = :enterpriseNo "
				;
		Query query = em.createQuery(sql);
		query.setParameter("customerID", customerID);
		query.setParameter("enterpriseNo", enterpriseNo);
		BigDecimal singleResult =  (BigDecimal) query.getSingleResult();
		return singleResult;
	}
	
}
